1. Field of the Invention
The present invention relates to a method, system, and program for implementing scrollable cursors in a database program to perform backward and forward scans.
2. Description of the Related Art
Prior art database programs include a feature referred to as cursors. A cursor is a named control structure used by an application program to point to a row of interest within some set of rows and to retrieve rows from the set, possibly making updates and deletions. A cursor points to rows from a database table that satisfy a structured query language (SQL) query against the table. The rows in the table that satisfy the SQL query comprise a result set of data. In dynamic cursors, the row is evaluated at the time it is fetched.
When a cursor is opened or initialized, the current row position of the cursor is the first record or row in the table or index that satisfies the query. The application program may then issue fetch commands to move the cursor and fetch forward one row at a time until the desired row is reached, i.e., the tenth qualifying entry from the current cursor position or the tenth qualifying entry from the top. In the prior art, if the application wanted to fetch a previous row from the current position, then the application would have to start from fetching from the beginning entry of the index and fetch forward until the desired row was found. This process is inefficient because the cursor has to fetch through rows that have already processed. For this reason, there is a need in the art for improved techniques for scrolling backward.
Moreover, in the current art, to fetch forward or backward, a runtime module submits requests to a data manager component that controls access to a database table to return a next entry. The runtime module would then determine whether the returned entry satisfied the predicates included with the cursor. The runtime module would further determine if the cursor has advanced the requested number of entries that satisfy the predicates. If not, the runtime module would request the data manager and/or index manager for the next entry. This process can be quite slow to move forward a large number of entries because the runtime module must make numerous calls to a data manager module and index manager module to return each entry in the table or index and then determine whether the entries satisfy the predicates included in any WHERE statements provided with the cursor.
Thus, there is a need in the art for an improved approach for moving with a cursor backwards or forwards through a database table or index that avoids the overhead of current techniques.
To address the shortcomings in the prior art discussed above, preferred embodiments disclose a method, system, and program for accessing records in a database object. The database object has rows and columns of values. A cursor is maintained that points to a start entry in the database object. A runtime component receives a command to return a kth entry in the object having column values that satisfy predicates in a query statement. The runtime component calls a manager component that accesses entries in the database object to return the kth entry in the object having column values that satisfy the predicates. The manager component moves the cursor through sequential entries in the database object until a kth entry that satisfies the predicates from the start entry is reached. The manager component positions the cursor to an entry that is the kth entry that satisfies the predicates from the start entry and returns data from the kth entry pointed to by the cursor to the runtime component.
In further embodiments, the database object comprises an index to a base table, wherein the index has a subset of columns from the base table. Further, the manager component comprises a data manager that manages access to the base table and an index manager that manages access to the index. The query statement includes a select list of columns to return from an entry whose columns also satisfy the predicates in the query.
In such case, moving the cursor through the sequential entries in the index further comprises, for each cursor movement determining, with the index manager, whether columns in the index entry pointed to by the cursor satisfy the predicates. The index manager further determines whether the cursor points to the entry that is the kth entry to satisfy the predicates from the start entry if the columns in the index satisfy the predicates. The index manager moves the cursor to point to the next entry in the index if the columns in the index entry pointed to by the cursor do not satisfy the predicates.
Still further, for each cursor movement, the index manager determines whether predicates must be applied to non-index columns in the base table to satisfy the predicates. If so, the data manager determines whether non-index columns in the entry in the base table corresponding to the index entry pointed to by the cursor satisfy the predicates on the non-index columns in the base table if the non-index columns must be considered in order to determine whether the predicates in the query statement are satisfied or not satisfied.
Preferred embodiments provide a technique for scrolling a cursor forward and backward through a database object, such as an index or database table, in an efficient manner that minimizes the amount of traffic among the different database components. With preferred embodiments, the determination of whether index and/or base table entries satisfy cursor predicates is determined by the data manager and/or index manager components, thereby minimizing the number of calls that need to be performed to complete a fetch forward or backward operation with a dynamic cursor.